package com.tianqu.tms.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.tianqu.tms.core.exception.TmsDaoException;
import com.tianqu.tms.dto.TransferSettlementDateDto;
import com.tianqu.tms.dto.TransferSettlementInfoDto;

public class TransferSettlementDateDaoImp extends TmsDao implements
		TransferSettlementDateDao {

	protected TransferSettlementDateDaoImp() throws TmsDaoException {
		super();
		// TODO Auto-generated constructor stub
	}

	@Override
	public TransferSettlementDateDto getDateInfo(int type,int insertUserId) throws TmsDaoException {
		// 开始掉数据
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sql = new StringBuffer("SELECT ");
		sql.append("ID, ");
		sql.append("STARTDATE, ");
		sql.append("ENDDATE,    ");
		sql.append("INSERTUSERID,    ");
		sql.append("SETTLESTATE    ");
		sql.append("FROM ");
		sql.append(TmsTableNames.TMS_TRANSFER_SETTLEMENT_DATE);
		sql.append("   WHERE SETTLESTATE =" + type + " ");
		sql.append("   AND INSERTUSERID =" + insertUserId + " ");

		TransferSettlementDateDto dto = null;
		try {

			ps = conn.prepareStatement(sql.toString());

			rs = ps.executeQuery();
			while (rs.next()) {
				dto = new TransferSettlementDateDto();
				dto.setId(rs.getInt("ID"));
				dto.setStartDate(rs.getTimestamp("STARTDATE"));
				dto.setEndDate(rs.getTimestamp("ENDDATE"));
				dto.setInsertUserId(rs.getInt("INSERTUSERID"));
				dto.setSettleState(rs.getInt("SETTLESTATE")); 
			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}
		return dto;
	}

	@Override
	public void updateSettleState(int insertUserId) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE ");
		sql.append(TmsTableNames.TMS_TRANSFER_SETTLEMENT_DATE);
		sql.append(" SET ");
		sql.append(" SETTLESTATE=3 ");
		sql.append(" WHERE ");
		sql.append(" SETTLESTATE=2 ");
		sql.append(" AND INSERTUSERID="+insertUserId+" ");
		try {
			
			ps = conn.prepareStatement(sql.toString());
			ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			super.close(rs, ps);
		}
	}

	@Override
	public void updateSettleStateOne(TransferSettlementDateDto dto)
			throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE ");
		sql.append(TmsTableNames.TMS_TRANSFER_SETTLEMENT_DATE);
		sql.append(" SET ");
		sql.append(" ENDDATE=?, ");
		sql.append(" SETTLESTATE=?, ");
		sql.append(" SETTLEDAYID=? ");
		sql.append(" WHERE ");
		sql.append(" ID=? ");
		try {
			ps = conn.prepareStatement(sql.toString());
			ps.setTimestamp(1, dto.getEndDate());
			ps.setInt(2, dto.getSettleState());
			ps.setInt(3,dto.getSettleDayId());
			ps.setInt(4, dto.getId());
			ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			super.close(rs, ps);
		}		
	}

	@Override
	public void insertTransferSettlement(TransferSettlementDateDto dto)
			throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			StringBuffer sql = new StringBuffer("INSERT INTO ");
			sql.append(TmsTableNames.TMS_TRANSFER_SETTLEMENT_DATE);
			sql.append("(");
			sql.append(" STARTDATE, ");
			sql.append(" INSERTUSERID, ");			
			sql.append(" SETTLESTATE  ");
			sql.append("  ) VALUES ( ");
			sql.append(" ?,?,? )");

			ps = conn.prepareStatement(sql.toString());
			
			ps.setTimestamp(1, dto.getStartDate());
			ps.setInt(2, dto.getInsertUserId());
			ps.setInt(3, dto.getSettleState());
			ps.executeUpdate();
			
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}		
	}

	@Override
	public List<TransferSettlementDateDto> getSettleDateInfo(int type,int insertUserId) throws TmsDaoException {
		List<TransferSettlementDateDto> innerCarList = new ArrayList<TransferSettlementDateDto>();
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT ");
		sql.append(" ID,  ");
		sql.append(" STARTDATE,  ");
		sql.append(" ENDDATE	 ");
		sql.append(" FROM ");
		sql.append(TmsTableNames.TMS_TRANSFER_SETTLEMENT_DATE);
		sql.append(" WHERE SETTLESTATE="+type+" ");
		sql.append(" AND INSERTUSERID="+insertUserId+" ");
		sql.append(" ORDER BY ENDDATE DESC ");

		try {
			ps = conn.prepareStatement(sql.toString());
			rs = ps.executeQuery();
			TransferSettlementDateDto dto = null;
			while (rs.next()) {
				dto = new TransferSettlementDateDto();
				dto.setId(rs.getInt("ID")); 
				dto.setStartDate(rs.getTimestamp("STARTDATE"));
				dto.setEndDate(rs.getTimestamp("ENDDATE"));
				innerCarList.add(dto);
			}
			return innerCarList;
		} 
		catch (SQLException e) {
			throw new TmsDaoException(e.toString());
			
		} finally {
			super.close(rs, ps);
		}
	}

	@Override
	public List<TransferSettlementDateDto> getSettleDateIdBySettleDayId(
			int settleDayId) throws TmsDaoException {
		List<TransferSettlementDateDto> innerCarList = new ArrayList<TransferSettlementDateDto>();
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT ");
		sql.append(" ID,  ");
		sql.append(" STARTDATE,  ");
		sql.append(" ENDDATE	 ");
		sql.append(" FROM ");
		sql.append(TmsTableNames.TMS_TRANSFER_SETTLEMENT_DATE);
		sql.append(" WHERE SETTLEDAYID="+settleDayId+" ");

		try {
			ps = conn.prepareStatement(sql.toString());
			rs = ps.executeQuery();
			TransferSettlementDateDto dto = null;
			while (rs.next()) {
				dto = new TransferSettlementDateDto();
				dto.setId(rs.getInt("ID")); 
				dto.setStartDate(rs.getTimestamp("STARTDATE"));
				dto.setEndDate(rs.getTimestamp("ENDDATE"));
				innerCarList.add(dto);
			}
			return innerCarList;
		} 
		catch (SQLException e) {
			throw new TmsDaoException(e.toString());
			
		} finally {
			super.close(rs, ps);
		}
	}

	@Override
	public TransferSettlementDateDto getDateInfoBySettleId(String type,
			int insertUserId) throws TmsDaoException {
		// 开始掉数据
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sql = new StringBuffer("SELECT ");
		sql.append("ID, ");
		sql.append("STARTDATE, ");
		sql.append("ENDDATE,    ");
		sql.append("INSERTUSERID,    ");
		sql.append("SETTLESTATE    ");
		sql.append("FROM ");
		sql.append(TmsTableNames.TMS_TRANSFER_SETTLEMENT_DATE);
		sql.append("   WHERE ID IN " + type + " ");
		sql.append("   AND INSERTUSERID =" + insertUserId + " ");

		TransferSettlementDateDto dto = null;
		try {

			ps = conn.prepareStatement(sql.toString());

			rs = ps.executeQuery();
			while (rs.next()) {
				dto = new TransferSettlementDateDto();
				dto.setId(rs.getInt("ID"));
				dto.setStartDate(rs.getTimestamp("STARTDATE"));
				dto.setEndDate(rs.getTimestamp("ENDDATE"));
				dto.setInsertUserId(rs.getInt("INSERTUSERID"));
				dto.setSettleState(rs.getInt("SETTLESTATE")); 
			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}
		return dto;
	}
}
